//
//  DatabaseManager.swift
//  UAKA
//
//  Created by lete on 2020/6/9.
//  Copyright © 2020 小毅. All rights reserved.
//

//import UIKit

//class DatabaseManager: NSObject {
//
//}

class DBTestModel: NSObject {
    var age: Int64?
    var name: String = ""
    
}

import Foundation
import SQLite

struct DatabaseManager {
    
    var db: Connection!
    
    init() {
        connectDatabase()
    }
    
    // 与数据库建立连接
    mutating func connectDatabase(filePath: String = "/Documents") -> Void {
        
        let sqlFilePath = NSHomeDirectory() + filePath + "/db.sqlite3"
        
        do { // 与数据库建立连接
            db = try Connection(sqlFilePath)
            debugPrint("与数据库建立连接 成功")
        } catch {
            debugPrint("与数据库建立连接 失败：\(error)")
        }
        
    }
    
    let TABLE_Test = Table("table_test") // 表名称
    let TABLE_Test_ID = Expression<Int64>("test_id") // 列表项及项类型
    let TABLE_Test_Age = Expression<Int64?>("test_age")
    let TABLE_Test_Name = Expression<String>("test_name")
    
    // 建表
    func tableTestCreate() -> Void {
        do { // 创建表TABLE_LAMP
            try db.run(TABLE_Test.create { table in
                table.column(TABLE_Test_ID, primaryKey: .autoincrement) // 主键自加且不为空
                table.column(TABLE_Test_Age)
                table.column(TABLE_Test_Name)
            })
            debugPrint("创建表 TABLE_LAMP 成功")
        } catch {
            debugPrint("创建表 TABLE_LAMP 失败：\(error)")
        }
    }
    // 删除表
    func tableTestDelete() -> Void {
        do { // 创建表TABLE_LAMP
            try db.run(TABLE_Test.delete())
            debugPrint("删除表 \(TABLE_Test) 成功")
        } catch {
            debugPrint("删除表 \(TABLE_Test) 失败：\(error)")
        }
    }
    // 插入
    func tableTestInsertItem(model: DBTestModel) -> Void {
        let insert = TABLE_Test.insert(TABLE_Test_Age <- model.age, TABLE_Test_Name <- model.name)
        do {
            let rowid = try db.run(insert)
            debugPrint("插入数据成功 id: \(rowid)")
        } catch {
            debugPrint("插入数据失败: \(error)")
        }
    }
    
    // 根据字段删除删除
    func tableTestDeleteItemWithCondition(name: String) -> Void {
        let item = TABLE_Test.filter(TABLE_Test_Name == name)
        do {
            if try db.run(item.delete()) > 0 {
                debugPrint("\(name) 删除成功")
            } else {
                debugPrint("没有发现条目 \(name)")
            }
        } catch {
            debugPrint("\(name) 删除失败：\(error)")
        }
    }
    // 根据字段更新
    func tableTestUpdateItemWithCondition( name: String, newName: String) -> Void {
        let item = TABLE_Test.filter(TABLE_Test_Name == name)
        do {
            if try db.run(item.update(TABLE_Test_Name <- newName)) > 0 {
                debugPrint("\(name) 更新成功")
            } else {
                debugPrint("没有发现条目 \(name)")
            }
        } catch {
            debugPrint("\(name) 更新失败：\(error)")
        }
    }
    // 遍历(查询整个表)
    func queryTableTestFindAll() -> Array<Any> {
        var resultArr:[Any] = [Any]()
        
        for item in (try! db.prepare(TABLE_Test)) {
            resultArr.append(item)
            //                debugPrint("灯光 遍历 ———— id: \(item[TABLE_LAMP_ID]), address: \(item[TABLE_LAMP_ADDRESS]), name: \(item[TABLE_LAMP_NAME]), colorValue: \(item[TABLE_LAMP_COLOR_VALUE]), lampType: \(item[TABLE_LAMP_LAMP_TYPE])")
        }
        return resultArr
    }
    //
    // 根据字段查询
    func readTableTestItemFindWithCondition(name: String) -> Any {
        var reslut: Any?
        
        for item in try! db.prepare(TABLE_Test.filter(TABLE_Test_Name == name)) {
//            let db = DBTestModel()
            //                debugPrint("\n读取（灯光）id: \(item[TABLE_LAMP_ID]), address: \(item[TABLE_LAMP_ADDRESS]), name: \(item[TABLE_LAMP_NAME]), colorValue: \(item[TABLE_LAMP_COLOR_VALUE]), lampType: \(item[TABLE_LAMP_LAMP_TYPE])")
            reslut = item
        }
        return reslut as Any
    }
    
    
    
    //    // ===================================== 灯光 =====================================
    //    let TABLE_LAMP = Table("table_lamp") // 表名称
    //    let TABLE_LAMP_ID = Expression<Int64>("lamp_id") // 列表项及项类型
    //    let TABLE_LAMP_ADDRESS = Expression<Int64>("lamp_address")
    //    let TABLE_LAMP_NAME = Expression<String>("lamp_name")
    //    let TABLE_LAMP_COLOR_VALUE = Expression<String>("lamp_colorValue")
    //    let TABLE_LAMP_LAMP_TYPE = Expression<Int64>("lamp_lampType")
    //
    //    // 建表
    //    func tableLampCreate() -> Void {
    //        do { // 创建表TABLE_LAMP
    //            try db.run(TABLE_LAMP.create { table in
    //                table.column(TABLE_LAMP_ID, primaryKey: .autoincrement) // 主键自加且不为空
    //                table.column(TABLE_LAMP_ADDRESS)
    //                table.column(TABLE_LAMP_NAME)
    //                table.column(TABLE_LAMP_COLOR_VALUE)
    //                table.column(TABLE_LAMP_LAMP_TYPE)
    //            })
    //            debugPrint("创建表 TABLE_LAMP 成功")
    //        } catch {
    //            debugPrint("创建表 TABLE_LAMP 失败：\(error)")
    //        }
    //    }
    //
    //    // 插入
    //    func tableLampInsertItem(address: Int64, name: String, colorValue: String, lampType: Int64) -> Void {
    //       let insert = TABLE_LAMP.insert(TABLE_LAMP_ADDRESS <- address, TABLE_LAMP_NAME <- name, TABLE_LAMP_COLOR_VALUE <- colorValue, TABLE_LAMP_LAMP_TYPE <- lampType)
    //
    //        do {
    //            let rowid = try db.run(insert)
    //            debugPrint("插入数据成功 id: \(rowid)")
    //        } catch {
    //            debugPrint("插入数据失败: \(error)")
    //        }
    //    }
    //
    //    // 遍历
    //    func queryTableLamp() -> Void {
    //        for item in (try! db.prepare(TABLE_LAMP)) {
    //            debugPrint("灯光 遍历 ———— id: \(item[TABLE_LAMP_ID]), address: \(item[TABLE_LAMP_ADDRESS]), name: \(item[TABLE_LAMP_NAME]), colorValue: \(item[TABLE_LAMP_COLOR_VALUE]), lampType: \(item[TABLE_LAMP_LAMP_TYPE])")
    //        }
    //    }
    //
    //    // 读取
    //    func readTableLampItem(address: Int64) -> Void {
    //
    //        for item in try! db.prepare(TABLE_LAMP.filter(TABLE_LAMP_ADDRESS == address)) {
    //            debugPrint("\n读取（灯光）id: \(item[TABLE_LAMP_ID]), address: \(item[TABLE_LAMP_ADDRESS]), name: \(item[TABLE_LAMP_NAME]), colorValue: \(item[TABLE_LAMP_COLOR_VALUE]), lampType: \(item[TABLE_LAMP_LAMP_TYPE])")
    //        }
    //
    //    }
    //
    //    // 更新
    //    func tableLampUpdateItem(address: Int64, newName: String) -> Void {
    //        let item = TABLE_LAMP.filter(TABLE_LAMP_ADDRESS == address)
    //        do {
    //            if try db.run(item.update(TABLE_LAMP_NAME <- newName)) > 0 {
    //                debugPrint("灯光\(address) 更新成功")
    //            } else {
    //                debugPrint("没有发现 灯光条目 \(address)")
    //            }
    //        } catch {
    //            debugPrint("灯光\(address) 更新失败：\(error)")
    //        }
    //    }
    //
    //    // 删除
    //    func tableLampDeleteItem(address: Int64) -> Void {
    //        let item = TABLE_LAMP.filter(TABLE_LAMP_ADDRESS == address)
    //        do {
    //            if try db.run(item.delete()) > 0 {
    //                debugPrint("灯光\(address) 删除成功")
    //            } else {
    //                debugPrint("没有发现 灯光条目 \(address)")
    //            }
    //        } catch {
    //            debugPrint("灯光\(address) 删除失败：\(error)")
    //        }
    //    }
    
}


/**
 // 数据库声明
 var database: Database!
 
 override func viewDidLoad() {
 super.viewDidLoad()
 
 let paths = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true)
 debugdebugPrint("数据库地址:===================")
 debugdebugPrint(paths)
 // 与数据库建立连接
 database = Database()
 
 // 建立列表（有列表后不再建立）
 database.tableLampCreate()
 
 // 插入两条数据
 database.tableLampInsertItem(address: 51, name: "灯光1", colorValue: "#FFFFFF", lampType: 0)
 database.tableLampInsertItem(address: 52, name: "灯光2", colorValue: "#AAAAAA", lampType: 1)
 
 // 遍历列表（检查插入结果）
 database.queryTableLamp()
 
 // 根据条件查询
 database.readTableLampItem(address: 52)
 
 // 修改列表项
 database.tableLampUpdateItem(address: 51, newName: "客厅大灯")
 
 // 遍历列表（检查修改结果）
 database.queryTableLamp()
 
 // 删除列表项
 database.tableLampDeleteItem(address: 52)
 
 // 遍历列表（检查删除结果）
 database.queryTableLamp()
 }
 
 */
